﻿/*********************************************************************************************************************
VanguardDocumentation, Procedure SelectEntity

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

if
	not exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'P'
			and o.name = 'SelectEntity'
	)
begin
	declare @SqlCommand as nvarchar(max);
	set @SqlCommand = '
		create procedure VanguardDocumentation.SelectEntity
		as raiserror (''Procedure SelectEntity is not implemented yet!'', 11, 1);
	';
	execute (@SqlCommand);
end;
GO

alter procedure VanguardDocumentation.SelectEntity
	/*****************************************************************************************************************
	Returns the first matching entity.
	*****************************************************************************************************************/
	
	@EntityName                 nvarchar(512),
		/* Dot-separated name of entity. If null then the result will be null as well (this is not an error but only 
		means we've selected the database). */
	@EntityType                 varchar(128)                = null,
		/* You can also choose a certain type of object. Not necessary if you want an entity with a unique name or 
		your entity is first in the search order (unique constraints and their assosciated indexes will have the same 
		name, but constraints comes before indexes in the search order). */
	@Level0Name                 nvarchar(128)               output,
	@Level0Type                 varchar(64)                 output,
	@Level1Name                 nvarchar(128)               output,
	@Level1Type                 varchar(64)                 output,
	@Level2Name                 nvarchar(128)               output,
	@Level2Type                 varchar(64)                 output
		/* Name and type of object found. These values are fed into sp_addextendedproperty, etc. */
	
	with execute as owner
as
begin
	
	set nocount on;
	set xact_abort on;
	set lock_timeout 3000;
	set transaction isolation level read committed;
	
	declare @ErrorNo as integer;
	declare @ErrorMsg as nvarchar(1536);
	declare @ErrorLine as integer;
	declare @ErrorState as integer;
	
	declare @FullEntityName as nvarchar(512);
	declare @TypeLevel as tinyint;
	
	begin try;
		
		-- Type 'Database' and/or a single dot in the name means the database is the entity
		if
			@EntityName = '.' and (@EntityType in ('DATABASE', '') or @EntityType is null)
			or
			@EntityType = 'DATABASE' and (@EntityName in ('.', '') or @EntityName is null)
		begin
			
			set @Level0Name = null;
			set @Level1Name = null;
			set @Level2Name = null;
			set @Level0Type = null;
			set @Level1Type = null;
			set @Level2Type = null;
			
			return;
			
		end;
		
		-- Add base entity if the given name is empty or begins with a dot (and is not a database).
		if
			(@EntityName like '.%' or @EntityName = '' or @EntityName is null)
		begin
			set @FullEntityName = VanguardDocumentation.GetBaseEntity() + coalesce(@EntityName, '');
		end
		else if
			not (@EntityName like '.%' or @EntityName = '')
		begin
			set @FullEntityName = @EntityName;
		end;
		
		-- If @FullEntityName hasn't been set, that means that @EntityName suggests a suffix to the base name, but either 
		-- the table #VanguardDocumentationBaseEntity doesn't exist or it is empty. This is an error.
		if
			@FullEntityName is null
		begin
			raiserror (740907, 16, 1, @EntityName);
			return;
		end;
		
		-- Parse entity name
		execute VanguardDocumentation.SplitEntityName
			@FullEntityName, @Level0Name out, @Level1Name out, @Level2Name out;
		
		-- Depending on the number of parts in the name, we will match the type with that level.
		if @Level0Name is null
			return; -- this is a database, so no type should be set
		else if @Level1Name is null
			set @TypeLevel = 0;
		else if @Level2Name is null
			set @TypeLevel = 1;
		else
			set @TypeLevel = 2;
		
		-- Parse entity type
		execute VanguardDocumentation.SplitEntityType
			@EntityType, @TypeLevel, @Level0Type out, @Level1Type out, @Level2Type out;
		
		-- Tries to match with an existing entity, through views LevelXEntities
		if
			@Level1Name is null
		begin
			
			select top (1) @Level0Type = Level0Type, @Level0Name = Level0Name
			from VanguardDocumentation.Level0Entities
			where Level0Name = @Level0Name and (@Level0Type is null or @Level0Type = Level0Type)
			order by SearchOrder;
			
		end
		else if
			@Level2Name is null
		begin
			
			select top (1)
				@Level0Type = Level0Type, @Level0Name = Level0Name,
				@Level1Type = Level1Type, @Level1Name = Level1Name
			from VanguardDocumentation.Level1Entities
			where Level0Name = @Level0Name and (@Level0Type is null or @Level0Type = Level0Type)
				and Level1Name = @Level1Name and (@Level1Type is null or @Level1Type = Level1Type)
			order by SearchOrder;
			
		end
		else
		begin
			
			select top (1)
				@Level0Type = Level0Type, @Level0Name = Level0Name,
				@Level1Type = Level1Type, @Level1Name = Level1Name,
				@Level2Type = Level2Type, @Level2Name = Level2Name
			from VanguardDocumentation.Level2Entities
			where Level0Name = @Level0Name and (@Level0Type is null or @Level0Type = Level0Type)
				and Level1Name = @Level1Name and (@Level1Type is null or @Level1Type = Level1Type)
				and Level2Name = @Level2Name and (@Level2Type is null or @Level2Type = Level2Type)
			order by SearchOrder;
			
		end;
		
		-- If no entity matched in this query throw an error that no entity has been found.
		if
			@@rowcount = 0
		begin
			raiserror (740908, 16, 2, @FullEntityName, @EntityType);
			return;
		end;
		
	end try
	begin catch;
		
		set @ErrorNo = error_number();
		set @ErrorMsg = left(error_message(), 1536);
		set @ErrorLine = error_line();
		set @ErrorState = error_state();
		
		raiserror (740900, 16, @ErrorState, 'SelectEntity', @ErrorNo, @ErrorLine, @ErrorMsg);
		
	end catch;
	
end;
GO




